package com.timber.util;

import java.util.ArrayList;
import java.util.StringTokenizer;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;

/**
 * Collection of simple utils for SQL statements manipulation.
 */
public final class SQLUtil {
    /**
     * Returns an optional column from the resultset. If no column found it
     * returns default value (defValue) and print a message to log.
     */
    public static String getString(ResultSet rs, String columnName, String defValue) {
        try {
            String value = rs.getString(columnName);
            if (rs.wasNull()) {
                return defValue;
            } else {
                return value;
            }
        } catch (SQLException e) {
            Util.debug("Column \"" + columnName +
                    "\" is missed or of unexpected type. " +
                    defValue + " was assumed." + e);
            return defValue;
        }
    }

    /**
     * Returns an optional column from the resultset. If no column found it
     * returns default value (defValue).
     */
    public static java.util.Date getDate(ResultSet rs, String columnName, java.util.Date defValue) {
        try {
            java.util.Date value = rs.getDate(columnName);
            if (rs.wasNull()) {
                return defValue;
            } else {
                return value;
            }
        } catch (SQLException e) {
            Util.debug("Column \"" + columnName +
                    "\" is missed or of unexpected type. " +
                    defValue + " was assumed." + e);
            return defValue;
        }
    }

    /**
     * Returns an optional column from the resultset. If no column found it
     * returns default value (defValue).
     */
    public static int getInt(ResultSet rs, String columnName, int defValue) {
        try {
            int value = rs.getInt(columnName);
            if (rs.wasNull()) {
                return defValue;
            } else {
                return value;
            }
        } catch (SQLException e) {
            Util.debug("Column \"" + columnName +
                    "\" is missed or of unexpected type. " +
                    defValue + " was assumed." + e);
            return defValue;
        }
    }

    /**
     * Returns an optional column from the resultset. If no column found it
     * returns default value (defValue).
     */
    public static double getDouble(ResultSet rs, String columnName, double defValue) {
        try {
            double value = rs.getDouble(columnName);
            if (rs.wasNull()) {
                return defValue;
            } else {
                return value;
            }
        } catch (SQLException e) {
            Util.debug("Column \"" + columnName +
                    "\" is missed or of unexpected type. " +
                    defValue + " was assumed." + e);
            return defValue;
        }
    }

    /**
     * Returns an optional column from the resultset. If no column found it
     * returns default value (defValue).
     */
    public static long getLong(ResultSet rs, String columnName, long defValue) {
        try {
            long value = rs.getLong(columnName);
            if (rs.wasNull()) {
                return defValue;
            } else {
                return value;
            }
        } catch (SQLException e) {
            Util.debug("Column \"" + columnName +
                    "\" is missed or of unexpected type. " +
                    defValue + " was assumed." + e);
            return defValue;
        }
    }

    /**
     * Validates and returns single "Y"/"N" char from the result set.
     */
    public static String getYesOrNo(ResultSet rs, String columnName, String defValue) {
        try {
            String s = rs.getString(columnName);
            if (s != null) {
                s = s.toUpperCase();
                return "Y".equals(s) ? s : "N";
            }
        } catch (SQLException e) {
            Util.debug("Column \"" + columnName +
                    "\" is missed or of unexpected type. " +
                    defValue + " was assumed." + e);
        }
        return defValue;
    }


    /**
     * This helper class used to print prepared statements to the log.
     */
    public static final class PreparedStatement {
        private ArrayList template;
        private ArrayList params;

        /**
         * Creates a prepared statement using the template
         */
        public PreparedStatement(String template) {
            StringTokenizer parser = new StringTokenizer(template + " ", "?");
            this.template = new ArrayList();
            params = new ArrayList();
            while (parser.hasMoreTokens()) {
                String token = parser.nextToken();
                if (token.length() > 0) {
                    this.template.add(token);
                    if (parser.hasMoreTokens()) {
                        this.params.add("?");
                    }
                }
            }
        }

        public void setParam(int index, String value) {
            if ((index > 0) && (index <= params.size())) {
                params.set(index - 1, value);
            }
        }

        public String toString() {
            StringBuffer result = new StringBuffer();
            for (int j = 0; j < template.size(); j++) {
                result.append(template.get(j));
                if (j < params.size()) {
                    result.append("[")
                          .append(params.get(j))
                          .append("]");
                }
            }
            return result.toString();
        }
    }

    public static void cleanup(Statement stmt, ResultSet rs) {
        cleanup(rs);
        cleanup(stmt);
    }

    public static void cleanup(Connection conn, Statement stmt, ResultSet rs) {
        cleanup(rs);
        cleanup(stmt);
        cleanup(conn);        
    }

    public static void cleanup(Connection conn){
        try {
            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch(SQLException e) {}
    }

    public static void cleanup(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
        } catch(SQLException e) {}
    }

    public static void cleanup(Statement stmt) {
        try {
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
        } catch(SQLException e) {}
    }


}
